﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class QuestionDAL
    {
        /// <summary>
        /// 获取题目类型列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetQuestionCategoryList()
        { 
            Dictionary<int, string> list = new Dictionary<int,string>();
            string sql = "SELECT ID, Name FROM E_QuestionCategory ";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取题目列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public QuestionListModel GetQuestionList(SearchQuestionModel searchModel, int pageSize, int pageIndex)
        {
            IList<ShortQuestionModel> list = new List<ShortQuestionModel>();
            QuestionListModel listModel = new QuestionListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", searchModel.CategoryID),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@IsRequired", searchModel.IsRequired),
                new SqlParameter("@IsPublic", searchModel.IsPublic),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getQuestionList", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortQuestionModel sqm = new ShortQuestionModel();
                        sqm.ID = (int)dr["ID"];
                        sqm.Title = dr["Title"].ToString();
                        sqm.CategoryName = dr["CategoryName"].ToString();
                        list.Add(sqm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取内训题目列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public QuestionListModel GetInternalQuestionList(SearchQuestionModel searchModel, int pageSize, int pageIndex)
        {
            IList<ShortQuestionModel> list = new List<ShortQuestionModel>();
            QuestionListModel listModel = new QuestionListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", searchModel.CategoryID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@IsRequired", searchModel.IsRequired),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getQuestionList", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortQuestionModel sqm = new ShortQuestionModel();
                        sqm.ID = (int)dr["ID"];
                        sqm.Title = dr["Title"].ToString();
                        sqm.CategoryName = dr["CategoryName"].ToString();
                        list.Add(sqm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取题目信息
        /// </summary>
        /// <param name="questionid"></param>
        /// <returns></returns>
        public QuestionModel GetQuestionInfo(int questionid)
        {
            string sql = @"SELECT     ID, Title, CategoryID, CourseID, ImgUrl, SoundUrl, Score, Description, KnowledgePoint, IsRequired
                           FROM         E_QuestionInfo
                           WHERE     (ID = " + questionid + ")";
            QuestionModel model = new QuestionModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.Score = (int)dr["Score"];
                    model.Title = dr["Title"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.Description = dr["Description"].ToString();
                    model.KnowledgePoint = dr["KnowledgePoint"].ToString();
                    model.IsRequired = (bool)dr["IsRequired"];
                    model.OptionList = GetOptionList(questionid);
                    model.AreaList = GetQuestionAreaList(questionid);
                }
                return model;
            }
        }

        /// <summary>
        /// 获取题目详细信息
        /// </summary>
        /// <param name="questionid"></param>
        /// <returns></returns>
        public QuestionModel GetQuestionDetailInfo(int questionid)
        {
            string sql = @"SELECT * FROM v_questioninfo WHERE (ID = " + questionid + ")";
            QuestionModel model = new QuestionModel();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.Score = (int)dr["Score"];
                    model.Title = dr["Title"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.Description = dr["Description"].ToString();
                    model.KnowledgePoint = dr["KnowledgePoint"].ToString();
                    model.IsRequired = (bool)dr["IsRequired"];
                    model.OptionList = GetOptionList(questionid);
                }
                return model;
            }
        }

        /// <summary>
        /// 获取题目详细信息及选项列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        public IList<QuestionModel> GetQuestionDetailInfoList(SearchQuestionModel searchModel)
        {
            IList<QuestionModel> list = new List<QuestionModel>();

            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", searchModel.Title),
                new SqlParameter("@CategoryID", searchModel.CategoryID),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@IsRequired", searchModel.IsRequired),
                new SqlParameter("@IsPublic", searchModel.IsPublic)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getQuestionDetailList", parms))
            {
                while (dr.Read())
                {
                    QuestionModel model = new QuestionModel();
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.Score = (int)dr["Score"];
                    model.Title = dr["Title"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.Description = dr["Description"].ToString();
                    model.KnowledgePoint = dr["KnowledgePoint"].ToString();
                    model.OptionList = GetOptionList(model.ID);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取题目详细信息及选项列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<QuestionModel> GetQuestionDetailInfoList(IList<int> id)
        {
            IList<QuestionModel> list = new List<QuestionModel>();
            string sql = "SELECT * FROM v_questioninfo WHERE " + DALUtils.GetInCondition("ID", id) + " ORDER BY CategoryID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    QuestionModel model = new QuestionModel();
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.Score = (int)dr["Score"];
                    model.Title = dr["Title"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.Description = dr["Description"].ToString();
                    model.KnowledgePoint = dr["KnowledgePoint"].ToString();
                    model.IsRequired = (bool)dr["IsRequired"];
                    model.OptionList = GetOptionList(model.ID);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取题目详细信息及选项列表，如果该用户未参加过考试，则插入考试信息，如果已经参加过却没有提交，则返回原先考试ID
        /// </summary>
        /// <param name="testModel"></param>
        /// <returns></returns>
        public IList<QuestionModel> GetQuestionDetailInfoListByParms(TestModel testModel)
        {
            IList<QuestionModel> list = new List<QuestionModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", testModel.UserID),
                new SqlParameter("@ExamID", testModel.ExamID),
                new SqlParameter("@QuestionIDArray", DALUtils.Convert2String(testModel.QuestionIDArray)),
                new SqlParameter("@CreatedTime", testModel.CreatedTime)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getQuestionDetailInfoListByParms", parms))
            {
                while (dr.Read())
                {
                    QuestionModel model = new QuestionModel();
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.Score = (int)dr["Score"];
                    model.Title = dr["Title"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.Description = dr["Description"].ToString();
                    model.KnowledgePoint = dr["KnowledgePoint"].ToString();
                    model.OptionList = GetOptionList(model.ID);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取题目详细信息及选项列表
        /// </summary>
        /// <param name="testModel"></param>
        /// <returns></returns>
        public IList<QuestionModel> GetQuestionDetailInfoListSelfByParms(TestModel testModel)
        {
            IList<QuestionModel> list = new List<QuestionModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@QuestionIDArray", DALUtils.Convert2String(testModel.QuestionIDArray))
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getQuestionDetailInfoListSelfByParms", parms))
            {
                while (dr.Read())
                {
                    QuestionModel model = new QuestionModel();
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.Score = (int)dr["Score"];
                    model.Title = dr["Title"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.Description = dr["Description"].ToString();
                    model.KnowledgePoint = dr["KnowledgePoint"].ToString();
                    model.OptionList = GetOptionList(model.ID);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取选项列表
        /// </summary>
        /// <param name="questionid"></param>
        /// <returns></returns>
        public IList<QuestionOptionModel> GetOptionList(int questionid)
        {
            IList<QuestionOptionModel> list = new List<QuestionOptionModel>();
            string sql = "SELECT ID, SerialNumber, OptionTitle, IsCorrect FROM E_QuestionOption WHERE (QuestionID = " + questionid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    QuestionOptionModel model = new QuestionOptionModel();
                    model.ID = (int)dr["ID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.OptionTitle = dr["OptionTitle"].ToString();
                    model.IsCorrect = (bool)dr["IsCorrect"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取题目地区列表
        /// </summary>
        /// <param name="questionid"></param>
        /// <returns></returns>
        public Dictionary<int,string> GetQuestionAreaList(int questionid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT b.ID, b.Name FROM E_Question2Area AS a LEFT OUTER JOIN E_Dict_Area AS b ON a.AreaID = b.ID WHERE (a.QuestionID = " + questionid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["ID"], dr["Name"].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 添加题目
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddQuestion(QuestionModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@CourseID", model.CourseID),
                new SqlParameter("@IsRequired", model.IsRequired),
                new SqlParameter("@ImgUrl", model.ImgUrl),
                new SqlParameter("@SoundUrl", model.SoundUrl),
                new SqlParameter("@Score", model.Score),
                new SqlParameter("@Description", model.Description),
                new SqlParameter("@KnowledgePoint", model.KnowledgePoint),
                new SqlParameter("@AddDate", model.AddDate)
            };
            string sql = "INSERT INTO E_QuestionInfo(Title,CategoryID,CourseID,ImgUrl,SoundUrl,Score,Description,KnowledgePoint,AddDate,IsRequired)VALUES(@Title,@CategoryID,@CourseID,@ImgUrl,@SoundUrl,@Score,@Description,@KnowledgePoint,@AddDate,@IsRequired);SELECT @@IDENTITY";
            int id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql, parms));
            int rst = QuestionOptionsHandle(id, model.OptionList);
            rst += QuestionAreaHandle(id, model.AreaIDArray);
            return rst;
        }

        /// <summary>
        /// 修改题目
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateQuestion(QuestionModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Title", model.Title),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@CourseID", model.CourseID),
                new SqlParameter("@IsRequired", model.IsRequired),
                new SqlParameter("@ImgUrl", model.ImgUrl),
                new SqlParameter("@SoundUrl", model.SoundUrl),
                new SqlParameter("@Score", model.Score),
                new SqlParameter("@Description", model.Description),
                new SqlParameter("@KnowledgePoint", model.KnowledgePoint)
            };
            string sql = "UPDATE E_QuestionInfo SET Title = @Title, CategoryID = @CategoryID, CourseID = @CourseID, Score = @Score, Description = @Description, KnowledgePoint = @KnowledgePoint, IsRequired = @IsRequired";

            if (!model.ImgUrl.Equals(""))
                sql += ", ImgUrl = @ImgUrl ";
            if (!model.SoundUrl.Equals(""))
                sql += ", SoundUrl = @SoundUrl ";

            sql += " WHERE ID = @ID";

            SqlHelper.ExecuteNonQueryBySql(sql, parms);
            int rst = QuestionOptionsHandle(model.ID, model.OptionList);
            rst += QuestionAreaHandle(model.ID, model.AreaIDArray);
            return rst;
        }

        /// <summary>
        /// 题目选项处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="modelList"></param>
        /// <returns></returns>
        public int QuestionOptionsHandle(int id, IList<QuestionOptionModel> modelList)
        {
            ArrayList sqllist = new ArrayList();
            sqllist.Add("DELETE FROM E_QuestionOption WHERE QuestionID = " + id);
            foreach (QuestionOptionModel model in modelList)
            {
                sqllist.Add("INSERT INTO E_QuestionOption(QuestionID,SerialNumber,OptionTitle,IsCorrect)VALUES(" + id + "," + model.SerialNumber + ",'" + model.OptionTitle + "'," + (model.IsCorrect ? 1 : 0) + ");");
            }
            return SqlHelper.ExecuteSqlTran(sqllist);
        }

        /// <summary>
        /// 题目地区处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="areaid"></param>
        /// <returns></returns>
        public int QuestionAreaHandle(int id, string[] areaid)
        {
            ArrayList sqllist = new ArrayList();
            sqllist.Add("DELETE FROM E_Question2Area WHERE QuestionID = " + id);
            foreach (string s in areaid)
            {
                sqllist.Add("INSERT INTO E_Question2Area(QuestionID,AreaID)VALUES(" + id + "," + s + ");");
            }
            return SqlHelper.ExecuteSqlTran(sqllist);
        }

        /// <summary>
        /// 删除题目
        /// </summary>
        /// <param name="questionid"></param>
        /// <returns></returns>
        public int DelQuestion(string[] questionid) 
        {
            string sql = "DELETE FROM E_QuestionOption WHERE " + DALUtils.GetInCondition("QuestionID", questionid) + "; DELETE FROM E_QuestionInfo WHERE " + DALUtils.GetInCondition("ID", questionid);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 根据类别获取题目ID
        /// </summary>
        /// <param name="categoryid"></param>
        /// <param name="areaid"></param>
        /// <param name="courseid"></param>
        /// <param name="isRequired">0为普通，1为必选</param>
        /// <returns></returns>
        public IList<int> GetQuestionIDByCategory(int categoryid, int areaid, int courseid, int isRequired)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT a.ID FROM  E_QuestionInfo AS a LEFT OUTER JOIN E_Question2Area AS b ON a.ID = b.QuestionID WHERE (a.CategoryID = " + categoryid + ") AND (b.AreaID = " + areaid + ") AND (a.CourseID = " + courseid + ") AND (a.IsRequired = " + isRequired + ") ORDER BY ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0]);
                }
                return list;
            }
        }

        /// <summary>
        /// 根据课程获取题目ID
        /// </summary>
        /// <param name="areaid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<int> GetQuestionIDByCourse(int areaid, int courseid)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT a.ID FROM  E_QuestionInfo AS a LEFT OUTER JOIN E_Question2Area AS b ON a.ID = b.QuestionID WHERE (a.CategoryID IN (2,3) ) AND (b.AreaID = " + areaid + ") AND (a.CourseID = " + courseid + ") ORDER BY ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0]);
                }
                return list;
            }
        }

        /// <summary>
        /// 根据类别获取题目ID（返回多列）
        /// </summary>
        /// <param name="categoryid"></param>
        /// <param name="areaid"></param>
        /// <param name="courseid"></param>
        /// <param name="isRequired">0为普通，1为必选</param>
        /// <returns></returns>
        public IList<QuestionModel> GetQuestionIDByCategory(int categoryid, int areaid, int courseid, int isRequired, bool bIsMultiData)
        {
            IList<QuestionModel> list = new List<QuestionModel>();
            string sql = "SELECT a.ID,a.Title FROM  E_QuestionInfo AS a LEFT OUTER JOIN E_Question2Area AS b ON a.ID = b.QuestionID WHERE (a.CategoryID = " + categoryid + ") AND (b.AreaID = " + areaid + ") AND (a.CourseID = " + courseid + ") AND (a.IsRequired = " + isRequired + ") ORDER BY ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    QuestionModel quesModel = new QuestionModel();
                    quesModel.ID = (int)dr[0];
                    quesModel.Title = (string)dr[1];
                    list.Add(quesModel);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取考试设置
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public Dictionary<int, int> GetExamSetting(int examid)
        {
            Dictionary<int, int> list = new Dictionary<int, int>();
            string sql = "SELECT QuestionCategoryID,QuestionQuantity FROM E_ExamSetting WHERE ExamID = " + examid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], (int)dr[1]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取当前题目类别所设置题目数的总分
        /// </summary>
        /// <param name="categoryid"></param>
        /// <param name="quantity"></param>
        /// <returns></returns>
        public int[] GetCurrentTotalScore(int categoryid, int quantity)
        {
            int[] score = new int[2] { 0, 0 };
            //string sql = "SELECT SUM(Score) AS Score FROM (SELECT TOP (" + quantity + ") Score FROM E_QuestionInfo WHERE (CategoryID = " + categoryid + "))";
            string sql = "SELECT SUM(Score) AS SUMScore, (SELECT DISTINCT TOP (1) Score FROM E_QuestionInfo WHERE (CategoryID = " + categoryid + ")) AS AVGScore FROM (SELECT TOP (" + quantity + ") Score FROM E_QuestionInfo WHERE (CategoryID = " + categoryid + ")) AS derivedtbl_1";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    score[0] = dr[0] is DBNull ? 0: (int)dr[0];
                    score[1] = (int)dr[1];
                }
            }
            //return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
            return score;
        }

        /// <summary>
        /// 获取试卷信息
        /// </summary>
        /// <param name="testid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public TestDetailInfoModel GetTestDetailInfo(int testid,int userid)
        {
            TestDetailInfoModel model = new TestDetailInfoModel();
            string sql = "SELECT a.ID, a.ExamID, a.QuestionIDArray, a.CreatedTime, b.ID AS TestLogId, b.UserID, b.TestID, b.StartTime, b.AnswerArray, b.Score, b.IsOver, b.SubmitTime, c.Title AS ExamTitle, b.SubjectiveQuestionAnswer,b.SubjectiveScore FROM E_Test AS a INNER JOIN E_TestLog AS b ON a.ID = b.TestID INNER JOIN E_Exam AS c ON a.ExamID = c.ID WHERE (b.UserID = " + userid + ") AND (a.ID = " + testid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.ExamID = (int)dr["ExamID"];
                    model.ExamTitle = dr["ExamTitle"].ToString();
                    model.StartTime = (DateTime)dr["StartTime"];
                    model.SubmitTime = (DateTime)dr["SubmitTime"];
                    model.Score = (int)dr["Score"];
                    model.QuestionList = GetQuestionList(model.ExamID, dr["QuestionIDArray"].ToString());
                    model.AnswerArray = dr["AnswerArray"].ToString();
                    model.TestLogId = (int)dr["TestLogId"];
                    model.SubjectiveAnswerArray = dr["SubjectiveQuestionAnswer"].Equals(DBNull.Value)?(new string[0]):(" " + dr["SubjectiveQuestionAnswer"].ToString() + " ").Split((new string[]{"$$"}),StringSplitOptions.RemoveEmptyEntries);
                    //wnan band subjective score
                    string[] tempSubjectScore = dr["SubjectiveScore"].Equals(DBNull.Value) ? (new string[0]) : dr["SubjectiveScore"].ToString().Split((new string[] { ";" }), StringSplitOptions.RemoveEmptyEntries);

                    if (model.SubjectiveAnswerArray.Length > 0)
                    {
                        int[] subjectiveScore = new int[model.SubjectiveAnswerArray.Length];
                        for (int i = 0; i < model.SubjectiveAnswerArray.Length; i++)
                        {
                            subjectiveScore[i] = int.Parse(tempSubjectScore.Length > 0?tempSubjectScore[i]:"0");
                        }

                        model.SubjectScore = subjectiveScore;
                    }
                    
                    
                    
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取试卷问题列表（已存在考试）
        /// </summary>
        /// <param name="examid"></param>
        /// <param name="idlist"></param>
        /// <returns></returns>
        public IList<ExamQuestionModel> GetQuestionList(int examid, string idlist)
        {
            IList<ExamQuestionModel> list = new List<ExamQuestionModel>();
            string sql = "SELECT DISTINCT a.QuestionCategoryID, b.Name AS QuestionCategoryName,b.DefaultScore FROM E_ExamSetting AS a INNER JOIN E_QuestionCategory AS b ON a.QuestionCategoryID = b.ID WHERE (a.ExamID = " + examid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    ExamQuestionModel model = new ExamQuestionModel();
                    model.CategoryID = (int)dr["QuestionCategoryID"];
                    model.CategoryName = dr["QuestionCategoryName"].ToString();
                    model.DefaultScore = (int)dr["DefaultScore"];
                    model.CategoryQuestionList = GetQuestionAndOptionListByQuestionIdList(model.CategoryID, idlist);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 根据问题ID获取列表
        /// </summary>
        /// <param name="categoryid"></param>
        /// <param name="idlist"></param>
        /// <returns></returns>
        public IList<QuestionModel> GetQuestionAndOptionListByQuestionIdList(int categoryid, string idlist)
        {
            IList<QuestionModel> list = new List<QuestionModel>();
            string questionSql = "SELECT ID, Title, ImgUrl, SoundUrl, Description,Score FROM E_QuestionInfo WHERE ID IN(" + idlist + ") AND (CategoryID = " + categoryid + ")";
            using (IDataReader qdr = SqlHelper.ExecuteDataReaderBySql(questionSql))
            {
                while (qdr.Read())
                {
                    QuestionModel model = new QuestionModel();
                    model.ID = (int)qdr["ID"];
                    model.Title = qdr["Title"].ToString();
                    model.ImgUrl = qdr["ImgUrl"].ToString();
                    model.SoundUrl = qdr["SoundUrl"].ToString();
                    model.Description = qdr["Description"].ToString();
                    model.MaxScore = (int)qdr["Score"];
                    model.OptionList = GetOptionList(model.ID);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取某一课程某一类别题目总数
        /// </summary>
        /// <param name="courseid"></param>
        /// <param name="categoryid"></param>
        /// <returns></returns>
        public int GetAllQuestionQuantity(int courseid, int categoryid)
        {
            string sql = "SELECT COUNT(ID) AS Expr1 FROM E_QuestionInfo WHERE (CategoryID = " + categoryid + ") AND (CourseID = " + courseid + ")";
            object count = SqlHelper.ExecuteScalarBySql(sql);
            return count == null ? 0 : (int)count;
        }

        /// <summary>
        /// 获取某一课程某一类别必选题目总数
        /// </summary>
        /// <param name="courseid"></param>
        /// <param name="categoryid"></param>
        /// <returns></returns>
        public int GetAllRequiredQuestionQuantity(int courseid, int categoryid)
        {
            string sql = "SELECT COUNT(ID) AS Expr1 FROM E_QuestionInfo WHERE (CategoryID = " + categoryid + ") AND (CourseID = " + courseid + ") AND (IsRequired = 1)";
            object count = SqlHelper.ExecuteScalarBySql(sql);
            return count == null ? 0 : (int)count;
        }

        public void UpdateSubjectiveScoreAndTotalScore(int testLogId,TestDetailInfoModel testDetailInfoModel)
        {
            string strSubjectiveScore = "";
            if (testDetailInfoModel.SubjectScore != null && testDetailInfoModel.SubjectScore.Length > 0)
            {
                for (int i = 0; i < testDetailInfoModel.SubjectScore.Length; i++)
                {
                    strSubjectiveScore += testDetailInfoModel.SubjectScore[i] + ";";
                }
                IDataParameter[] parms = new IDataParameter[] { 
                    new SqlParameter("@testLogId", testLogId),
                    new SqlParameter("@subjectiveScore", strSubjectiveScore),
                };
                SqlHelper.ExecuteNonQueryByProcecdure("sp_updateSubjectiveScore", parms);
            }

        }

        public IList<QuestionCategoryModel> GetAllQuestionCategoryModel()
        {
            string sql = "select ID,Name,DefaultScore from E_QuestionCategory";
            IList<QuestionCategoryModel> list = new List<QuestionCategoryModel>();
            using (IDataReader qdr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (qdr.Read())
                {
                    QuestionCategoryModel model = new QuestionCategoryModel();
                    model.ID = (int)qdr["ID"];
                    model.Name = qdr["Name"].ToString();
                    model.DefaultScore = (int)qdr["DefaultScore"];
                    list.Add(model);
                }
                return list;
            }
        }

        public void UpdateQuestionCategoryDefaultScore(QuestionCategoryModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@DefaultScore", model.DefaultScore)
            };
            string sql = "update E_QuestionCategory set DefaultScore=@DefaultScore where ID=@ID";
            SqlHelper.ExecuteScalarBySql(sql, parms);
        }
    }
}
